﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Aspose.Cells;
using Newtonsoft.Json.Linq;

namespace ESMAM.Tools
{
    /// <summary>
    /// Excel操作类
    /// </summary>
    public class ExcelUtils
    {
        /// <summary>
        /// 返回水平对齐类型
        /// </summary>
        /// <param name="align">水平对齐类型</param>
        /// <returns></returns>
        private static TextAlignmentType GetTextAlign(string align)
        {
            switch (align.ToLower())
            {
                case "center":
                    return TextAlignmentType.Center;
                case "left":
                    return TextAlignmentType.Left;
                case "right":
                    return TextAlignmentType.Right;
                default: return TextAlignmentType.Center;
            }
        }

        /// <summary>
        /// 字体
        /// </summary>
        private static string Def_FontName = "黑体";
        /// <summary>
        /// 首行 行高（单位：Pixel）
        /// </summary>
        private static int FirstHeight = 45;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="lstSettings">所有要导出的列的在Excel中的配置信息</param>
        /// <param name="data">数据</param>
        /// <param name="isFirstFreeze">首行是否固定</param>
        public static void ExportlExcel(List<ExportSetting> lstSettings, DataTable data, bool isFirstFreeze, string fileName)
        {
            Workbook wk = new Workbook();
            var sheet = wk.Worksheets["Sheet1"];
            Cells cells = sheet.Cells;

            if (isFirstFreeze)
            {
                sheet.FreezePanes(1, 1, 1, 0);//冻结第一行
            }
            int rowNum = data.Rows.Count;//表格行数

            Dictionary<string, Style> dictStyle = new Dictionary<string, Style>();
            for (int j = 0; j < lstSettings.Count; j++)
            {
                var setting = lstSettings[j];
                Style style = wk.CreateStyle();
                style.HorizontalAlignment = GetTextAlign(setting.Align);
                style.VerticalAlignment = TextAlignmentType.Center;
                style.Font.IsBold = true;
                style.IsTextWrapped = true;
                style.Font.Name = Def_FontName;
                cells[0, j].PutValue(setting.Title);
                if (setting.Width.HasValue)
                {
                    cells.SetColumnWidthPixel(j, setting.Width.Value);
                }
                cells.SetRowHeightPixel(0, FirstHeight);
                cells[0, j].SetStyle(style);
                dictStyle.Add(setting.Name, style);
            }

            for (int j = 0; j < rowNum; j++)
            {
                DataRow dr = data.Rows[j];
                for (int k = 0; k < lstSettings.Count; k++)
                {
                    var setting = lstSettings[k];
                    object objVal = string.Empty;

                    Style style = dictStyle[setting.Name];
                    style.Font.IsBold = false;
                    style.IsTextWrapped = true;

                    if (data.Columns[setting.Name] != null)
                    {
                        objVal = dr[setting.Name];

                        if (setting.Format != null)
                        {
                            objVal = setting.Format(objVal);
                        }
                        else if (setting.Type == "select")
                        {
                            objVal = FormatValue(objVal, setting.Items, setting.ItemAttr);
                        }
                        else if (setting.FormatByParam != null)
                        {
                            objVal = setting.FormatByParam(objVal, setting.Pattern);
                        }
                    }
                    else
                    {
                        if (setting.FormatByRow != null)
                        {
                            objVal = setting.FormatByRow(dr.ToDictionary());
                        }
                    }

                    if (j == 0 && (objVal == null || string.IsNullOrEmpty(objVal.ToString())))
                    {
                        objVal = " ";
                    }
                    cells[j + 1, k].PutValue(objVal);
                    cells[j + 1, k].SetStyle(style);
                }
            }
            wk.Save(System.Web.HttpContext.Current.Response, fileName, ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Xlsx));
        }

        /// <summary>
        /// 下拉列表的数据缓存
        /// </summary>
        private static Dictionary<string, Dictionary<string, object>> Dict_Select_Json = new Dictionary<string, Dictionary<string, object>>();

        /// <summary>
        /// 格式化数据
        /// </summary>
        /// <param name="items">下拉列表数据</param>
        /// <param name="value">当前数据</param>
        /// <returns></returns>
        private static object FormatValue(object value, dynamic items, dynamic itemAttr)
        {
            string oldValue = string.Empty;
            if (value == null || string.IsNullOrEmpty(value.ToString()))
            {
                oldValue = string.Empty;
            }
            else
            {
                oldValue = value.ToString();
            }

            JObject jObjAttr = null;
            if (itemAttr != null)
            {
                jObjAttr = itemAttr as JObject;
            }

            object newValue = string.Empty;
            JArray arr = (items as JArray);
            string md5 = arr.ToString(Newtonsoft.Json.Formatting.None).Md5Hash();
            Dictionary<string, object> dict = null;
            if (!Dict_Select_Json.TryGetValue(md5, out dict))
            {
                dict = new Dictionary<string, object>();
                foreach (var item in arr)
                {
                    JObject jObj = (item as JObject);
                    if (itemAttr == null)
                    {
                        foreach (var kv in jObj)
                        {
                            dict.Add(kv.Key, (kv.Value as JValue).Value);
                        }
                    }
                    else
                    {
                        string key = (jObj[(jObjAttr["value"] as JValue).Value] as JValue).Value.ToString();
                        object val = (jObj[(jObjAttr["label"] as JValue).Value] as JValue).Value;
                        dict.Add(key, val);
                    }
                }
                Dict_Select_Json.Add(md5, dict);
            }
            dict.TryGetValue(oldValue, out newValue);
            return newValue;
        }
    }
}
